﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Assessment.Management.UI.Admin
{
    public partial class BackupandRestore : System.Web.UI.Page
    {
        string connStr = @"Data Source=ColdZero-VAIO\SQLEXPRESS;Initial Catalog=FormManagement;Integrated Security=True";
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                PopulateDatabaseTables();

            }

        }

        /// <summary>

        /// Populate database tables first

        /// </summary>

        private void PopulateDatabaseTables()
        {
            string tableName = string.Empty;

            string sql = "SELECT *, name AS table_name " +

            " FROM sys.tables WHERE Type = 'U' ORDER BY table_name";

            using (SqlConnection conn = new SqlConnection(connStr))
            {

                using (DataTable table = new DataTable())
                {

                    conn.Open();

                    using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
                    {

                        dAd.Fill(table);

                    }

                    ListBox1.DataSource = table;

                    ListBox1.DataBind();

                }

            }

        }
        protected void BackUpNow(object sender, EventArgs e)
        {

            string tableName = ListBox1.SelectedValue;

            using (DataSet dSetBackup = new DataSet())
            {

                using (SqlConnection conn = new SqlConnection(connStr))
                {

                    using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
                    {

                        dAd.Fill(dSetBackup, tableName);

                    }

                }

                dSetBackup.WriteXml(Server.MapPath("Backup/" + tableName + ".xml"));

                lblMessage.Text = "Backup for table <b>" + tableName + "</b> successful!";

            }

        }
        protected void RestoreNow(object sender, EventArgs e)
        {

            string restoreConnStr = @"Data Source=ColdZero-VAIO\SQLEXPRESS;Initial Catalog=FormManagement;Integrated Security=True";


            string tableName = ListBox1.SelectedValue;

            using (SqlConnection conn = new SqlConnection(restoreConnStr))
            {

                using (DataSet dSetBackup = new DataSet())
                {

                    // get the schema of the selected table from the database to restore

                    using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
                    {

                        // Following line will get the schema of the selected table in the dSetBackup DataSet

                        dAd.Fill(dSetBackup, tableName);

                        // get the data for selected table from backed up XML file

                        using (DataSet dSet = new DataSet())
                        {

                            dSet.ReadXml(Server.MapPath("Backup/" + tableName + ".bak"));

                            // Loop through all rows of the selected table and add into dSetBackup DataSet

                            foreach (DataRow row in dSet.Tables[0].Rows)
                            {
                                dSetBackup.Tables[0].NewRow();
                                dSetBackup.Tables[0].Rows.Add(row.ItemArray);
                            }

                            // Create a command builder to update dSetBackup DataSet
                            SqlCommandBuilder cmd = new SqlCommandBuilder(dAd);
                            // Following update command will push all added rows of dSetBackup DataSet into the database
                            dAd.Update(dSetBackup, tableName); // We are done !!!
                        }
                    }
                    lblMessage.Text = "Restore of table <b>" + tableName + "</b> successful!";
                }

            }

        }
        protected void RestoreNowAll(object sender, EventArgs e)
        {

            string restoreConnStr = @"Data Source=ColdZero-VAIO\SQLEXPRESS;Initial Catalog=FormManagement;Integrated Security=True";

            string tableName = "Accounts,Classes,Config,Departments,Groups,IPAdress,MenuManagers,OptionHit,Options,Questions,Subjects,Teachers,Teacher_Class";

            using (SqlConnection conn = new SqlConnection(restoreConnStr))
            {

                using (DataSet dSetBackup = new DataSet())
                {

                    // get the schema of the selected table from the database to restore

                    using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
                    {

                        // Following line will get the schema of the selected table in the dSetBackup DataSet

                        dAd.Fill(dSetBackup, tableName);



                        // get the data for selected table from backed up XML file

                        using (DataSet dSet = new DataSet())
                        {

                            dSet.ReadXml(Server.MapPath("Backup/CSDL.Xml"));

                            // Loop through all rows of the selected table and add into dSetBackup DataSet

                            //foreach (DataRow row in dSet.Tables[0].Rows)
                            //{

                            //    dSetBackup.Tables[0].NewRow();

                            //    dSetBackup.Tables[0].Rows.Add(row.ItemArray);

                            //}



                            // Create a command builder to update dSetBackup DataSet

                            SqlCommandBuilder cmd = new SqlCommandBuilder(dAd);



                            // Following update command will push all added rows of dSetBackup DataSet into the database

                            dAd.Update(dSetBackup, tableName); // We are done !!!

                        }

                    }

                    lblMessage.Text = "Restore of table <b>CSDL</b> successful!";

                }

            }

        }

        protected void BackUpAllNow(object sender, EventArgs e)
        {
            string tableName = "Accounts,Classes,Config,Departments,Groups,IPAdress,MenuManagers,OptionHit,Options,Questions,Subjects,Teachers,Teacher_Class";

            using (DataSet dSetBackup = new DataSet())
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
                    {
                        dAd.Fill(dSetBackup, tableName);
                    }
                }
                dSetBackup.WriteXml(Server.MapPath("Backup/CSDL.Xml"));
                lblMessage.Text = "Backup for table <b>CSDL</b> successful!";
            }
        }
    }
}